import configparser
import pymysql
import os
import hashlib

CODE_INDEX = 0
DATE_INDEX = 1
OPEN_INDEX = 2
OPEN_HFQ_INDEX = 3
OPEN_HFQ_BY_FACTOR_INDEX = 4
CLOSE_INDEX = 5
CLOSE_HFQ_INDEX = 6
CLOSE_HFQ_BY_FACTOR_INDEX = 7
HIGH_INDEX = 8
HIGH_HFQ_INDEX = 9
HIGH_HFQ_BY_FACTOR_INDEX = 10
LOW_INDEX = 11
LOW_HFQ_INDEX = 12
LOW_HFQ_BY_FACTOR_INDEX = 13
AMPLITUDE_INDEX = 14
AMPLITUDE_HFQ_INDEX = 15
AMPLITUDE_HFQ_BY_FACTOR_INDEX = 16
FLUCTUATE_INDEX = 17
FLUCTUATE_HFQ_INDEX = 18
FLUCTUATE_HFQ_BY_FACTOR_INDEX = 19
VOLUME_INDEX = 20
AMOUNT_INDEX = 21
TURNOVER_INDEX = 22

STOCK_INFO_DB_FIELDS = '`code`, `date`, `open`, `open_hfq`, `open_hfq_by_factor`, '\
                   '`close`, `close_hfq`, `close_hfq_by_factor`, `high`, `high_hfq`, `high_hfq_by_factor`, '\
                   '`low`, `low_hfq`, `low_hfq_by_factor`, `amplitude`, `amplitude_hfq`, `amplitude_hfq_by_factor`, '\
                   '`fluctuate`, `fluctuate_hfq`, `fluctuate_hfq_by_factor`, `volume`, `amount`, `turnover`'

def get_mysql_conn_config():
    """
    从配置文件中获取mysql连接信息
    """
    quant_config = configparser.ConfigParser()
    current_file_path = os.path.abspath(__file__)
    current_dir_path = os.path.dirname(current_file_path)
    project_root = os.path.abspath(os.path.join(current_dir_path, os.pardir, os.pardir))
    config_path = project_root + '/config/quant_config.ini'
    quant_config.read(config_path)
    return quant_config['mysql']

def new_db_conn():
    """
    创建一个mysql连接
    """
    config = get_mysql_conn_config()
    return pymysql.connect(host=config['host'],
                           user=config['user'],
                           password=config['password'],
                           database=config['database'])

def get_all_stock_code_set():
    """
    获取所有股票代码
    :return: 股票代码set
    """
    db = new_db_conn()
    cursor = db.cursor()
    cursor.execute('SELECT `code` FROM code_to_name')
    data = cursor.fetchall()
    code_set = set()
    for row in data:
        code_set.add(row[0])
    cursor.close()
    db.close()
    return code_set

def get_all_stock_day_k_info(code):
    table_name = get_stock_hist_day_k_table_name(code)
    db = new_db_conn()
    cursor = db.cursor()
    cursor.execute('SELECT {} FROM {} '
                   'WHERE `code` = %s ORDER BY `date` ASC'.format(STOCK_INFO_DB_FIELDS, table_name), code)
    data = cursor.fetchall()
    cursor.close()
    db.close()
    return data

def get_all_stock_week_k_info(code):
    table_name = get_stock_hist_week_k_table_name(code)
    db = new_db_conn()
    cursor = db.cursor()
    cursor.execute('SELECT {} FROM {} '
                   'WHERE `code` = %s ORDER BY `date` ASC'.format(STOCK_INFO_DB_FIELDS, table_name), code)
    data = cursor.fetchall()
    cursor.close()
    db.close()
    return data

def get_all_stock_month_k_info(code):
    table_name = get_stock_hist_month_k_table_name(code)
    db = new_db_conn()
    cursor = db.cursor()
    cursor.execute('SELECT {} FROM {} '
                   'WHERE `code` = %s ORDER BY `date` ASC'.format(STOCK_INFO_DB_FIELDS, table_name), code)
    data = cursor.fetchall()
    cursor.close()
    db.close()
    return data

def get_all_stock_quarter_k_info(code):
    table_name = get_stock_hist_quarter_k_table_name(code)
    db = new_db_conn()
    cursor = db.cursor()
    cursor.execute('SELECT {} FROM {} '
                   'WHERE `code` = %s ORDER BY `date` ASC'.format(STOCK_INFO_DB_FIELDS, table_name), code)
    data = cursor.fetchall()
    cursor.close()
    db.close()
    return data

def get_all_stock_year_k_info(code):
    table_name = get_stock_hist_year_k_table_name(code)
    db = new_db_conn()
    cursor = db.cursor()
    cursor.execute('SELECT {} FROM {} '
                   'WHERE `code` = %s ORDER BY `date` ASC'.format(STOCK_INFO_DB_FIELDS, table_name), code)
    data = cursor.fetchall()
    cursor.close()
    db.close()
    return data

def get_stock_hist_day_k_table_name(code):
    """
    根据股票代码获取日k线所在的表名
    """
    return 'stock_hist_day_k_' + str(int(hashlib.md5(code.encode()).hexdigest(), 16) % 20)

def get_stock_hist_week_k_table_name(code):
    """
    根据股票代码获取周k线所在的表名
    """
    return 'stock_hist_week_k_' + str(int(hashlib.md5(code.encode()).hexdigest(), 16) % 5)

def get_stock_hist_month_k_table_name(code):
    """
    根据股票代码获取月k线所在的表名
    """
    return 'stock_hist_month_k'

def get_stock_hist_quarter_k_table_name(code):
    """
    根据股票代码获取季k线所在的表名
    """
    return 'stock_hist_quarter_k'

def get_stock_hist_year_k_table_name(code):
    """
    根据股票代码获取年k线所在的表名
    """
    return 'stock_hist_year_k'

def get_stock_feature_day_table_name(code):
    """
    根据股票代码获取特征所在的表名
    """
    return 'stock_feature_day_' + str(int(hashlib.md5(code.encode()).hexdigest(), 16) % 20)

def get_stock_label_day_table_name(code):
    """
    根据股票代码获取label所在的表名
    """
    return 'stock_label_day_' + str(int(hashlib.md5(code.encode()).hexdigest(), 16) % 20)

def get_stock_code_to_id_map():
    """
    获取股票code到id的映射关系
    """
    db = new_db_conn()
    cursor = db.cursor()
    cursor.execute('SELECT `id`, `code`, `name` FROM code_to_name')
    data = cursor.fetchall()
    cursor.close()
    db.close()
    if len(data) == 0:
        raise Exception('Please execute StockInfoFetcher.build_or_update_code_name first')
    code_to_id_map = dict()
    for row in data:
        code_to_id_map[row[1]] = row[0]
    return code_to_id_map